#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : settings.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/1/15 16:14                 #
#                                                                   #
#                   @Last Update  : 2021/1/15 16:14                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
SqllawdatadeduplestatementS = [
    '''DELETE FROM `lawtext` WHERE DownLoadWebNameC != "资源合作" AND ((SortA ="LAWCOUNTRYXJP" AND DownLoadWebNameC != "新加坡法律在线") 
    OR (SortA = "LAWCOUNTRYFLB" AND DownLoadWebNameC != "菲律宾法律和法律体系数据库" AND DownLoadWebNameC != "菲律宾最高法院电子图书馆") 
    OR (SortA = "LAWCOUNTRYWL" AND DownLoadWebNameC != "文莱总检查院办公室") OR (SortA = "LAWCOUNTRYMD" AND DownLoadWebNameC != "缅甸Pyithu Hluttaw网站") 
    OR (SortA = "LAWCOUNTRYJPZ" AND Website != "Mojgov Law-regular" AND DownLoadWebNameC != "柬埔寨商务部网站")) 
    AND SortA != "LAWCOUNTRYTG" AND SortA != "LAWCOUNTRYYDNXY" AND ID NOT IN (SELECT * FROM ((SELECT MAX(ID) FROM `lawtext` WHERE DownLoadWebNameC != "资源合作" 
    AND ((SortA ="LAWCOUNTRYXJP" AND DownLoadWebNameC != "新加坡法律在线") 
    OR(SortA = "LAWCOUNTRYFLB" AND DownLoadWebNameC != "菲律宾法律和法律体系数据库" AND DownLoadWebNameC != "菲律宾最高法院电子图书馆") 
    OR (SortA = "LAWCOUNTRYWL" AND DownLoadWebNameC != "文莱总检查院办公室") OR (SortA = "LAWCOUNTRYMD" AND DownLoadWebNameC != "缅甸Pyithu Hluttaw网站") 
    OR (SortA = "LAWCOUNTRYJPZ" AND Website != "Mojgov Law-regular" AND DownLoadWebNameC != "柬埔寨商务部网站")) 
    AND SortA != "LAWCOUNTRYTG" AND SortA != "LAWCOUNTRYYDNXY" GROUP BY UPPER(LegalName),Organizaation,PublishDate,EffectiveDate,SortA,Keyword,ChapNo) AS a))''',
    '''DELETE FROM `lawtext` WHERE LegalName != '' AND SortA = 'LAWCOUNTRYXJP' AND DownLoadWebNameC = "新加坡法律在线" AND 
    ID NOT IN (SELECT * FROM ((SELECT MIN(ID) FROM `lawtext` WHERE SortA = 'LAWCOUNTRYXJP' GROUP BY UPPER(LegalName),
    ChapNo,UPPER(PublishDate),UPPER(EffectiveDate),Website) AS a))''',
    '''DELETE FROM `lawtext` WHERE ChapNo LIKE "%No.%" AND SortA = "LAWCOUNTRYFLB" AND (DownLoadWebNameC = 
    "菲律宾法律和法律体系数据库" OR DownLoadWebNameC = "菲律宾最高法院电子图书馆") AND ID NOT IN (SELECT * FROM ((SELECT MIN(ID) FROM 
    `lawtext` WHERE SortA = "LAWCOUNTRYFLB" GROUP BY EffectiveDate,UPPER(ChapNo)) AS a))''',
    '''DELETE FROM `lawtext` WHERE SYS_FLD_DIGITFILENAME = "." AND SortA = "LAWCOUNTRYTG"''',
    '''DELETE FROM `lawtext` WHERE SortA = "LAWCOUNTRYYN" AND DownLoadWebNameC = "越南规范文件法律数据库" AND ID NOT IN 
    (SELECT * FROM ((SELECT MAX(ID) FROM `lawtext` WHERE SortA = "LAWCOUNTRYYN" GROUP BY UPPER(LegalName),ChapNo,
    PublishDate,EffectiveDate) AS a))''',
    '''DELETE FROM `lawtext` WHERE SortA = "LAWCOUNTRYLW" AND DownLoadWebNameC = "老挝官方公报" AND ID NOT IN (SELECT * 
    FROM ((SELECT MAX(ID) FROM `lawtext` WHERE SortA = "LAWCOUNTRYLW" GROUP BY UPPER(LegalName),PublishDate,
    EffectiveDate)AS a))''',
    '''DELETE FROM `lawtext` WHERE SortA = "LAWCOUNTRYMD" AND DownLoadWebNameC = "缅甸Pyithu Hluttaw网站" AND ID NOT IN (
    SELECT * FROM ((SELECT MIN(ID) FROM `lawtext` WHERE SortA = "LAWCOUNTRYMD" GROUP BY UPPER(LegalName),ChapNo,
    PublishDate,EffectiveDate)AS a))''',
    '''DELETE FROM `lawtext` WHERE SortA = "LAWCOUNTRYJPZ" AND (DownLoadWebNameC = "柬埔寨王国司法部网站" OR DownLoadWebNameC = "柬埔寨商务部网站") 
    AND ID NOT IN (SELECT * FROM ((SELECT MIN(ID) FROM `lawtext` WHERE SortA = "LAWCOUNTRYJPZ" 
    AND (DownLoadWebNameC = "柬埔寨王国司法部网站" OR DownLoadWebNameC = "柬埔寨商务部网站") GROUP BY UPPER(LegalName),PublishDate,EffectiveDate)AS a))'''
]
SqlcasedatadeduplestatementS = [
    '''DELETE FROM `lawcasetext` WHERE SortA = "LAWCOUNTRYFLB" AND CaseNumber != "" AND 
    (DownLoadWebNameE = "Supreme Court E-Library" OR DownLoadWebNameE = "PHILIPPINE LAWS AND JURISPRUDENCE DATABANK") 
    AND ID NOT IN (SELECT * FROM ((SELECT MIN(ID) FROM `lawcasetext` WHERE SortA = "LAWCOUNTRYFLB" AND CaseNumber != "" GROUP BY CaseNumber,AdjudicationDate) AS a))''',
    # '''DELETE FROM `lawcasetext` WHERE SortA = "LAWCOUNTRYYDNXY" and ID NOT IN (SELECT * FROM ((SELECT MIN(ID) FROM
    # `lawcasetext` where SortA = "LAWCOUNTRYYDNXY" GROUP BY UPPER(Title),CaseNumber,KeyWord,SortA,SortC,People,
    # CaseOfAction,UseLaw,AdjudicationDate,JudgAgency) AS a))''',
    '''DELETE FROM `lawcasetext` WHERE SortA != "LAWCOUNTRYYDNXY" and DownLoadWebNameC != "资源合作" and ID NOT IN (SELECT * FROM ((SELECT MIN(ID) FROM 
    `lawcasetext` where SortA != "LAWCOUNTRYYDNXY" and DownLoadWebNameC != "资源合作" GROUP BY UPPER(Title),CaseNumber,KeyWord,SortA,SortC,People,
    CaseOfAction,UseLaw,AdjudicationDate,JudgAgency) AS a))'''
]